#!/usr/bin/env python3
# -*- coding:utf-8 -*-
import json
import datetime
import time
import logging
import sys
import os
import subprocess
import pymysql
import json
from sanic import Sanic, response
import uvloop
import asyncio
import mysqlconn
import opsjwt
import hashlib
import io
from openpyxl import load_workbook
import pandas as pd


# pip3 install Sanic

def getmenu(request):
    # rr=check_token(request)
    # if(rr["code"]!=0):
    #   return response.json(rr)

    # print("getmenu:")
    workcode = datetime.datetime.now().strftime("%Y%m%d%H%M%S.%f")
    try:
        # 第一步：打开数据库连接
        db = pymysql.connect(host="192.168.0.67",
                             user="root",
                             password="blm123",
                             port=3306,  # 端口
                             database="oto_saas_maintenance",
                             charset='utf8')

        sql = "SELECT a.menu_id AS amenu_id, JSON_OBJECT( 'a.name', a.NAME, 'children', JSON_OBJECT( 'b.name', b.NAME, 'children', JSON_OBJECT( 'c.name', c.NAME ) ) ) AS result FROM ( SELECT menu_id, NAME FROM tb_sys_menu WHERE type = 0 ) AS a LEFT JOIN ( SELECT parent_id, menu_id, NAME FROM tb_sys_menu WHERE type = 1 ) b ON a.menu_id = b.parent_id LEFT JOIN ( SELECT parent_id, menu_id, NAME FROM tb_sys_menu WHERE type = 2 ) AS c ON b.menu_id = c.parent_id;"

        mysqlcursor1 = db.cursor()
        mysqlcursor1.execute(sql)
        # 获取所有记录列表
        results = mysqlcursor1.fetchall()
        list_menu = []
        for row in results:
            dict_menu = row[1]
            # print("dict_menu:", str(dict_menu))
            list_menu.append(dict_menu)

            # list_menu['children']['children']
        print("dict_menu:", str(list_menu))

        # 关闭mysql游标和连接
        mysqlcursor1.close()

        return response.json(dict_menu)
    except  Exception as err:
        loginfo = 'workcode:【' + workcode + '】,【---执行数据库异常---】,异常信息为:【' + str(err) + '】'
        # 若异常返回
        print('error', loginfo)

# ckk = getmenu('test')

def login(request):
    dict_rr = {}
    workcode = datetime.datetime.now().strftime("%Y%m%d%H%M%S.%f")

    print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f"), workcode, "收到接口请求[/login]:")
    try:
        requestbody = request.json
        # email = str(requestbody["email"])
        email = 'miaojie@otosaas.com'
        email_json = json.loads(email)
        ip = request.socket[0]
        password = hashlib.md5(requestbody[email_json]).encode(encoding='UTF-8').hexdigest()
        # password = hashlib.md5(str(requestbody["password"]).encode(encoding='UTF-8')).hexdigest()
        print(password)
        print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f"), workcode, requestbody)
        print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f"), workcode, "执行数据库查询")
        mysql = mysqlconn.MyPymysqlPool("reportmysql")
        print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f"), workcode, "生成mysql对象")

        # sqlone = "select aa.uid as userid,aa.username,aa.realname,aa.userstatus,GROUP_CONCAT(bb.menuurl) as menucode from (select a.uid,a.username,a.realname,GROUP_CONCAT(b.sysmenulist) as sysmenulist,a.userstatus from t_user a LEFT JOIN t_user_role b on CONCAT(',',a.roleidlist,',') like CONCAT('%,',b.uid,',%') and b.isdel=0 where a.isdel=0 and a.username='"+username+"' and a.userpwd='"+password+"')aa left join t_sysmenu bb on CONCAT(',',aa.sysmenulist,',') like CONCAT('%,',bb.uid,',%') and bb.isdel=0;"
        sqlone = "select * from tb_sys_user_new"
        print(sqlone)
        paramsql=(email,password)
        print(111111)
        print(paramsql)
        result = mysql.getOne(sqlone)
        print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f"), workcode, "结束数据库查询")
        dict_userinfo = {}
        # return paramsql

        if (result["userid"] != None):
            # generate_jwt_token(user_id, mobile=17339809807, email='miaojie@otosaas.com', userName='苗杰')

            dict_userinfo["userid"] = result["user_id"]
            dict_userinfo["mobile"] = result["mobile"],
            dict_userinfo["email"] = str(result["email"], encoding='utf-8')
            dict_userinfo["userName"] = str(result["user_name"], encoding='utf-8')

            token = opsjwt.generate_jwt_token(dict_userinfo["userid"], dict_userinfo["mobile"], dict_userinfo["email"],
                                              dict_userinfo["userName"], dict_userinfo["menucode"])

            print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f"), workcode, "token:", token)
            dict_rr["code"] = 0
            dict_rr["success"] = "true"
            dict_rr["message"] = "登录成功！"
            dict_rr["token"] = token

            mysql.dispose()

        else:
            dict_rr["code"] = 403
            dict_rr["success"] = "false"
            dict_rr["message"] = "登录失败，请检查您的用户名或密码"

        dict_rr["userinfo"] = dict_userinfo


    except  Exception as err:
        loginfo = 'workcode:【' + workcode + '】,【---执行数据库异常---】,异常信息为:【' + str(err) + '】'
        # 若异常返回
        print('error', loginfo)
        dict_rr["code"] = 500
        dict_rr["success"] = "false"
        dict_rr["message"] = "登录失败，请检查您的用户名或密码"
    return response.json(dict_rr)
ckk = login('test')